﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections.Specialized;
using MySql.Data.MySqlClient;

namespace Framework.Db
{
    /// <summary>
    /// 数据库基类
    /// </summary>
    public class DbBase
    {
        public string tb;
        public DataSet ds = null;

    
        public bool insertSQL(NameValueCollection DataL)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                string strField = "";
                string strValu = "";
                DataRow dr;
                foreach (String s in DataL.AllKeys)
                {
                    dr = CheckField(s);
                    if (dr == null)
                    {
                        return false;
                    }
                    strField += String.Format("{0},", s);
                    object type = dr["Type"].ToString().Split('(')[0];
                    strValu += string.Format("{0},", BuildValue(type.ToString(), DataL[s]));
                }
                sql.Append(string.Format("INSERT INTO {0}({1}) VALUES ({2})", tb, strField.Substring(0, strField.Length - 1), strValu.Substring(0, strValu.Length - 1)));
                return DbHelper.CreateDP().ExecuteNonQuery(sql.ToString()) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.ToString());
            }
            return false;
        }

        /// <summary>
        /// 执行Insert、Delete、Update的SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>布尔</returns>
        public bool queryNonSQL(string sql)
        {
            return DbHelper.CreateDP().ExecuteNonQuery(sql.ToString()) > 0 ? true : false;
        }

        /// <summary>
        /// 执行有数据集的SQL,返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>DataTable对象</returns>
        public DataTable queryDtSQL(string sql)
        {
            return DbHelper.CreateDP().RetriveDataSet(sql.ToString()).Tables[0];
        }

        /// <summary>
        /// 返回数据集的第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>Object</returns>
        public object queryOneSQL(string sql)
        {
            return DbHelper.CreateDP().ExecuteScalar(sql.ToString());
        }

        /// <summary>
        /// 检查字段是否存在
        /// </summary>
        /// <param name="field">字段名</param>
        /// <returns>存在：true,不存在：false</returns>
        DataRow CheckField(string field)
        {
            string sql = string.Format("desc {0} {1}", tb, field);
            ds = Framework.Db.DbHelper.CreateDP().RetriveDataSet(sql);
            return ds.Tables[0].Rows[0];
        }

        /// <summary>
        /// 构建SQL的VALUE属性
        /// </summary>
        /// <param name="type"></param>
        /// <param name="val"></param>
        /// <returns></returns>
        string BuildValue(string type, string val)
        {
            string str = "";

            switch (type)
            {
                case "varchar":
                    str = "'" + val + "'";
                    break;
                case "int":
                    str = val;
                    break;
            }
            return str;
        }
    }
}
